#!/usr/bin/python

## database.py - Database connectivity
## Copyright (C) January 26, 2005 Erick P. Bodine
## email: erick_bodine@comcast.net
## webaddress
##
## This program is free software; you can redistribute it and/or
## modify it under the terms of the GNU General Public License
## as published by the Free Software Foundation; either version 2
## of the License, or any later version.
##
## This program is distributed in the hope that it will be useful,
## but WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
## GNU General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with this program; if not, write to the Free Software
## Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.


# NOTE: It would be nice to abstract away the database types (metakit, sqlite, etc.)
#  This could be accomplished using *.prop file key/value + import checks + mixins (?)
# NOTE: The database file should get created empty if it doesn't exist w/out any problem
# NOTE: (sqlite) The timestamp/date/time data types require the use of mx.DateTime
#  This will require changing DateTime objects back to strings upon retrieval from the database.
#  The database will take a date|date-time string and convert it into an mx.DateTime string
#  when you enter it into the database.


"""
NAME
sql_backend.py

DESCRIPTION
The sqlite backend module.

AUTHOR
Erick P Bodine
"""

import os
import sys
import logging
import sqlite
from mx import DateTime

import FocusExceptions

__author__ = "$Author: ebodine $"
__date__ = "$Date: 2005-04-25 22:36:40 -0600 (Mon, 25 Apr 2005) $"
__version__ = "$Revision: 32 $"

log = logging.getLogger("")

PROJECTS_NAME = "projects"
CREATE_PROJECTS_TABLE = "create table %s(Name text, TasksTable text, LogsTable text)" % PROJECTS_NAME
CREATE_TASKS_TABLE = "create table %s_tasks(Title text, Comments text, StartDate date, EndDate date)"
CREATE_LOGS_TABLE = "create table %s_logs(LogDate date, Body text)"


class BaseDatabase:
    """ A base class for general database methods
    Attributes:
    database = Name of the database to use
    """
    def __init__(self, database):
        self.database = database
        if not os.path.isfile(database):
            log.debug("Unable to find the database file (%s), creating..." % database)
            db = sqlite.connect(database)
            cursor = db.cursor()
            cursor.execute(CREATE_PROJECTS_TABLE)
            db.commit()

    def getTableName(self, projectName, table):
        """ Return the table name associated with the project specified
        Params:
            projectName = Name of the project
            table = The logs/tasks table to get (LogTable or TasksTable)
        Return:
            String containing name of the logs or tasks table
        """
        sqlQuery = "SELECT %s FROM projects WHERE Name=\'%s\'" % (table, projectName)
        return self._retrieveSql(sqlQuery)[0][0]
        
    def computeEstimatedTime(self, startDate, endDate):
        """ Returns the ETA for a task
        If the end_date is empty just return a '+', also able to return a
        negative number.
        Params:
            startDate = The starting date
            endDate = The ending date 
        """
        result = '0'
        if end_date == "" or end_date == None:
            return result
        return result
        
    #--------------------------------------------------#
    #                Private Methods                   #
    #--------------------------------------------------#
            
    def _executeSql(self, sqlList=None):
        """ Execute a list of SQL commands.
        1) Connect to the database
        2) Get a cursor
        3) Execute the SQL
        4) Commit to the database
        
        Params:
            slqList = A list of SQL commands
        """
        db = sqlite.connect(self.database)
        cursor = db.cursor()
        for sql in sqlList:
            log.debug("SQL Command: \"%s\"" % sql)
            cursor.execute(sql)
        db.commit()
        
    def _retrieveSql(self, sqlQuery):
        """ Run the passed SQL query and returned the output
        Params:
            sqlQuery = The SQL query to execute
        Returns:
            A list of the results. Its structure is dependent on
            the SQL query that was executed.
        """
        log.debug("SQL Query: \"%s\"" % sqlQuery)
        db = sqlite.connect(self.database)
        cursor = db.cursor()
        cursor.execute(sqlQuery)
        
        return cursor.fetchall()
        

class DatabaseTransaction(BaseDatabase):
    """ A class around all of the necessary Database stuff. 
    If the database does not exist, an empty one will be created.
    Attributes:
        database = Name of the database to use
    """ 
    def __init__(self, database):
        BaseDatabase.__init__(self, database)
                
    #-- Project related methods --#
            
    def createProject(self, projectName):
        """ Create a project
        If there is an already so named project, the DuplciateTableName
        exception is raised.
        Params:
            projectName = THe name of the project to create
        """
        
        # This is because sqlite doesn't handle spaces well
        pnList = projectName.split(' ')
        if pnList > 1:
            projectName = '_'.join(pnList)
        
        log.debug("Creating project: %s" % projectName)
        db = sqlite.connect(self.database)
        cursor = db.cursor()

        # Check for duplicate project table name on creation
        sqlQuery = "SELECT Name from %s WHERE Name=\"%s\"" % (PROJECTS_NAME, projectName)
        if len(self._retrieveSql(sqlQuery)) != 0:
            raise FocusExceptions.DuplicateTableName, "Already another project named: (%s)" % projectName
        
        cursor.execute(CREATE_TASKS_TABLE % projectName)
        cursor.execute(CREATE_LOGS_TABLE % projectName)
        cursor.execute("insert into projects(Name, TasksTable, LogsTable) values(%s, %s, %s)", 
                        (projectName,"%s_tasks" % projectName, "%s_logs" % projectName))
        db.commit()
        
    def renameProject(self, oldProject, newProject):
        """ Rename the project table to the new project table name.
        Params:
            oldProject = The old project name
            newProject = The new project name
        """
        # This requires essentially removing the existing row from the 'projects' table PLUS,
        #  renaming the '<name>_tasks' & '<name>_logs' tables
        
        # This is because sqlite doesn't handle spaces well
        pnList = newProject.split(' ')
        if pnList > 1:
            newProject = '_'.join(pnList)
        
    def deleteProject(self, projectName):
        """ Delete the specified project and its associated task and logs
        tables.
        Params:
            projectName = The project to delete
        """
        log.debug("Deleting project: %s" % projectName)
        sql_query = "Select TasksTable, LogsTable from projects WHERE Name=\"%s\"" % projectName
        db = sqlite.connect(self.database)
        cursor = db.cursor()
        cursor.execute(sql_query)
        results = cursor.fetchall()
        
        # Drop any tasks &/or logs tables
        try:
            for table in results[0]:
                log.debug("Droping table: %s" % table)
                cursor.execute("Drop Table %s" % table)
                
        except IndexError:
            pass
            
        cursor.execute("Delete from %s where Name=\"%s\"" % (PROJECTS_NAME, projectName))
        db.commit()
        log.debug("Finished deleting: %s" % projectName)
        
    def getProjects(self):
        """ Retrieve a list of all the projects in the database. 
        Returns:
            A list of projects
        """
        sql = "Select Name from %s" % PROJECTS_NAME
        projectsList = self._retrieveSql(sql)
        
        projects = []
        for t in projectsList:
            projects.append(t[0])
        
        return projects
        
    #-- Tasks related methods --#
    
    def addTask(self, projectName, taskInfo):
        """ Add the task to the <projectName>_tasks table.
        Params:
            projectName = Name of the project
            taskInfo = A list containing the task information
        """
        log.info("Adding task to %s..." % projectName)
        tasksTable = self.getTableName(projectName, 'TasksTable')
        taskString = "(" + ", ".join([ "\"" + x + "\"" for x in taskInfo ]) + ")"
        sqlInsert = ["INSERT INTO %s VALUES %s" % (tasksTable, taskString)]
        self._executeSql(sqlInsert)
        
    def deleteTask(self, projectName, task):
        """ Delete the passed task
        Params:
            projectName = Name of the project
            task = The task to delete
        """
        log.info("Deleting task %s ..." % task)
        
        
        
    def getProjectTasks(self, projectName):
        """ Retrieving a list of tasks
        Params:
            projectName = Name of the project
        Return:
            A list of the tasks in the passed project
        """
        log.info("Retrieving task list from %s " % projectName)
        tasksTable = self.getTableName(projectName, 'TasksTable')
        sqlQuery = "SELECT Title FROM %s" % tasksTable
        return [ y[0] for y in self._retrieveSql(sqlQuery) ]
        
    def getTaskInfo(self, projectName, task):
        """ Get the information for the passed task
        Params:
            projectName = Name of the project
            task = The task 'Title'
        Return:
            A list of the task information
        """
        log.info("Retrieving task information for task: (%s)" % task)
        tasksTable = self.getTableName(projectName, 'TasksTable')
        sqlQuery = "SELECT * FROM %s WHERE Title=\"%s\"" % (tasksTable, task)
        return self._retrieveSql(sqlQuery)[0]
        
    def getAllTasks(self):
        """ Return the tasks for all projects
        Returns:
            a dictionary of all tasks
        """
        pass
        
    #-- Logs related methods --#
    
    def addLog(self, projectName, logInfo):
        """ Add log
        Params:
            projectName = Name of the project
            logInfo = 
        """
        log.info("Adding log...")
        log_table = self.getTableName(projectName, 'LogsTable')
        logString = "(" + ", ".join([ "\"" + x + "\"" for x in logInfo ]) + ")"
        sql = ["INSERT INTO %s VALUES %s" % (log_table, logString)]
        self._executeSql(sql)
        
    def deleteLog(self, projectName, logDate):
        pass
        
    def changeLog(self, projectName, oldLog, newLog):
        pass
        
    def getProjectLogs(self, projectName):
       """ Retrieve a list of the logs
       Params:
           projectName = Name of the project
       Return:
           A list of the logs in the passed project
       """
       log.info("Getting all of the logs for project: (%s)" % projectName)
       log_table = self.getTableName(projectName, 'LogsTable')
       sqlQuery = "SELECT LogDate FROM %s" % log_table
       return [ y[0] for y in self._retrieveSql(sqlQuery) ]
        
    def getLogInfo(self, projectName, log_):
        """ Retrieve the info associated with the specified log
        Params:
            projectName = Name of the project
            log_ = The specifc log to retrieve
        Return:
            tuple of log information (log_, comments)
        """
        log.info("Getting log (%s) information ..." % log_)
        log_table = self.getTableName(projectName, 'LogsTable')
        sqlQuery = "SELECT * FROM %s WHERE LogDate=\"%s\"" % (log_table, log_)
        return self._retrieveSql(sqlQuery)[0]
        
    def getProjectLogs(self, projectName):
        pass
        
if __name__ == '__main__':
    
    # Setup the logging
    handler = logging.StreamHandler()
    log.setLevel(logging.DEBUG)
    format = logging.Formatter("%(asctime)s %(levelname)-3s [%(module)s:%(lineno)d] %(message)s")
    handler.setFormatter(format)
    log.addHandler(handler)
    
    import pprint    # Make data structures look pretty
    
    module = os.path.basename(sys.argv[0])
    print "\n** Testing %s **\n" % (module+" "+ __version__+" "+__date__)
    
    # Remve the test database
    TEST_DB = "test_database"
    if os.path.isfile(TEST_DB):
        try:
            os.remove(TEST_DB)
        except OSError, error:
            assert "Unable to remove %s: %s" % (TEST_DB, str(error))
        
    #-- some test data --#
    test_data = {   'test1': { 'task1': [],
                                'log1': []
                    },
                    'test2': { 'task2': [],
                                'log2': []
                    },
                    'test3': { 'task3': [],
                                'log3': []
                    }
                }
                    
    projects_data = ('test1', 'test2', 'test3')
    print "TEST: projects_data: ", projects_data
    
    DT = DatabaseTransaction(TEST_DB)
    
    for p in projects_data:
        DT.createProject(p)
        
    projectsList = DT.getProjectsList()
    print "TEST: projectsList: ", projectsList
    
    print "TEST: Deleting the second project..."
    DT.deleteProject(projects_data[1])
    
    log_info = ['2004-07-06 18:50:12.12', 'some text for the log']
    log_info2 = ['2005-01-26 18:12:12.12', 'some text for log2']
    DT.addLog(projects_data[0], log_info)
    print "TEST: getLogInfo: ",DT.getLogInfo(projects_data[0], log_info[0])

    DT.addLog(projects_data[0], log_info2)
    print "TEST: getProjectLogs: ", DT.getProjectLogs(projects_data[0])

    print "TEST: Adding tasks..."
    DT.addTask(projects_data[0], ["project1_task", "a test task for project1",
                                  '2004-07-06 18:50:12.12', '2004-07-08 16:30:10.12'])
    DT.addTask(projects_data[0], ["project1_task2", "a second test task for project1",
                                  '2004-07-06 18:50:12.12', '2004-07-08 16:30:10.12'])
    
    print "TEST: Getting the tuple of tasks..."
    print "TEST: poject tasks: "
    pprint.pprint(DT.getProjectTasks(projects_data[0]))
